/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.zjdt.epserver.db.dao4mysql;

import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 *
 * @author dusin
 */
public class MisceDaoImpl {

    static Logger logger = Logger.getLogger(MisceDaoImpl.class);
    private JdbcTemplate jdbcTemplate;

    public MisceDaoImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    
    //检查推荐码是否正确
    public boolean checkRecommendCode(String code){
        String sql = "select * from constant where name='recommendCode' and value=? ";                
        List list = jdbcTemplate.queryForList(sql,code);
        return list.size()>0;
    }
   
    public boolean setConstant(String name,String value) {
        boolean flag = true;
        String sql = null;
        sql = "update constant set value=? where name=?";
        int rows = jdbcTemplate.update(sql, value,name);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    public String getConstant(String name){
        String sql = "select value from constant where name=?";                
        String v = jdbcTemplate.queryForObject(sql,String.class,name);
        return v;
    }
    
    
    
    public List<Map<String, Object>> findSiterStat(String dateFrom,String dateTo) {
        String sql = "select sum(checi) as CheCis,sum(pfront) as PFronts,sum(pside) as Psides,sum(plicense) as PLicenses,sum(ppaper) as PPapers ,\n"
                + "sum(netWeight) as netWeights,\n"
                + "sum(isQuan) as isQuanS,\n"
                + "fa.name as KHName,\n"
                + "\n"
                + "siterID,\n"
                + "si.name as ZCYName,\n"
                + "companyCode,\n"
                + "co.name as GYSName\n"
                + "\n"
                + "from \n"
                + "\n"
                + "(SELECT\n"
                + "left(arrivalTime,8) AS Date,\n"
                + "1 as CheCi,\n"
                + "1-ISNULL(frontphoto) as pfront,\n"
                + "1-ISNULL(sidePhoto) as pside,\n"
                + "1-ISNULL(licensePhoto) as plicense,\n"
                + "1-ISNULL(paperPhoto) as ppaper,\n"
                + "\n"
                + "case when ISNULL(netWeight) then 0 else netWeight end as netWeight,\n"
                + "MOD(status,2) as isQuan,\n"
                + "\n"
                + "acargo.factoryCode,\n"
                + "acargo.siterID,\n"
                + "acargo.companyCode\n"
                + "\n"
                + "from acargo\n"
                + ")  cargo \n"
                + "\n"
                + "LEFT OUTER JOIN factory fa on cargo.factorycode=fa.code\n"
                + "LEFT OUTER JOIN siter si on cargo.siterID=si.ID\n"
                + "LEFT OUTER JOIN mycompany co on cargo.companyCode=co.code\n"
                + "\n"
                + "where 1=1 and date >=? and date<=?\n"
                + "\n"
                + "group by cargo.factoryCode,cargo.siterID,cargo.CompanyCode";
        
        return jdbcTemplate.queryForList(sql,dateFrom,dateTo);
    }
}
